I am working with Php and Oracle on a project and would like to know if there are any shortcuts for this problem.
I have three tables that are related to each other. The application is a simplistic(ish) Job request form. The three tables I have is Requisition_Form which holds the bulk of the information about the job request. The primary key in this is an automatically incremented number field (using a trigger that calls a sequence).
The job form allows a user to specify any number of people to receive applications which are stored in another table identified with the Requisition_Form's primary key, and the employees ID number.
A job also has multiple job duties, of which is stored in yet another table (again, since there could be 1 job duty, or 100), also of which the Primary key consists of the auto incremented key.
So my question is how do I get this number easily after inserting all the information into the Requisition form. My only solution is to insert it and do a query to get the value, and then insert the rest of the information into the other tables.
Is there a more eloquent solution to this? Some function that PHP and/or oracle offers that allows you to retrieve this information quickly and easily?
leaving out error handling on oci_parse(), etc, which i'll assume for the moment you already are doing correctly, the way to do this is to use a RETURNING clause for your insert statement and get the returned value by binding a variable to it.
$query = 'insert into requisition_form (blah blah blah) returning requisition_form_id into :out0';
$stmt = oci_parse($connection, $query);
$out = array(0=>null);
oci_bind_by_name($stmt, ':out0', $out[0], 999); // last param should be at least the max length of the column holding the value you're retrieving
oci_execute($stmt);
oci_commit($connection);